<?php
require_once ROOT_LIBRARY_LAZADA . DS . 'benchmark.php';

class models_warehouse {

    const SHIPPED = 5;

    private $_db;
    private $_cache;

    public function __construct($db, $cache) {
        $this->_db = $db;
        $this->_cache = $cache;
    }

    public function get_stock($sku) {
        if (isset($sku)) {
            $result = $this->_db->queryFirst(
                    "
                    SELECT COUNT(p.sku) as STOCK
                    FROM oms_live.wms_inventory i 
                    INNER JOIN oms_live.ims_product p ON i.fk_product = p.id_product 
                    WHERE i.fk_inventory_status = 1 AND (p.sku = :sku)
                    GROUP BY p.sku
                  ", array(
                ':sku' => "$sku"
            ));
            $result = $result === FALSE ? 0 : $result;
            $exce = $this->_db->getPDOException();
            if ($exce) {
                throw $exce;
            }
            return $result;
        }
    }

    public function check_exist_sku($sku) {
        $results = $this->_db->selectFirst('ims_product', array(
            'sku' => $sku
        ));
        return $results ? TRUE : FALSE;
    }

    public function get_order_id($order_number) {
        $result = $this->_db->queryFirst("
            SELECT id_sales_order,customer_first_name, fk_sales_order_status, customer_last_name, order_nr,payment_method,created_at
            FROM ims_sales_order WHERE order_nr = :order_nr 
        ", array(
            ':order_nr' => $order_number
        ));
        if (isset($result))
            return $result;
        return null;
    }
	
	public function get_status_history_order($id_sales_order){
        $results = $this->_db->query("
            SELECT fk_sales_order_status, timestmp
            FROM ims_sales_order_status_history 
            WHERE fk_sales_order = :fk_sales_order
            ORDER BY timestmp DESC
        ", array(
            ':fk_sales_order' => $id_sales_order
        ));
        
        $list_status = array();
        if (isset($results)){
            foreach ($results as $status) {
                $list_status[] = $status['fk_sales_order_status'];
            }
        }
        return $list_status;
    }

    public function get_info_item_order($id_sales_order) {
        $results = $this->_db->query("                    
             SELECT tmp2.*, COUNT(sku) as amount
             FROM (     
                    SELECT tmp.*, shipment_provider_name FROM (
                            SELECT o.order_nr, si.`name`, si.sku, op.id_package,
                                    pd.fk_shipment_provider, pd.tracking_number,
                                    t_his.fk_sales_order_item_status,
                                    si.id_sales_order_item,
                                    t_his.created_at
                            FROM ims_sales_order_item si
                            INNER JOIN ims_sales_order o ON o.id_sales_order = si.fk_sales_order
                            LEFT JOIN oms_package_item pi ON pi.fk_sales_order_item = si.id_sales_order_item
                            LEFT JOIN oms_package op ON pi.fk_package = op.id_package
                            LEFT JOIN oms_package_dispatching pd ON pd.fk_package = op.id_package
                            JOIN ims_sales_order_item_status_history t_his ON si.id_sales_order_item = t_his.fk_sales_order_item
                            WHERE o.id_sales_order = :id_sales_order
                            ORDER BY t_his.created_at DESC
                        ) tmp LEFT JOIN oms_shipment_provider p ON p.id_shipment_provider = tmp.fk_shipment_provider
                        GROUP BY id_sales_order_item
                     ) tmp2 
                     GROUP BY id_package,sku
                     ORDER BY sku DESC
                ", array(
            ':id_sales_order' => $id_sales_order
        ));

        $exce = $this->_db->getPDOException();
        if ($exce) {
            throw $exce;
        }
        return $results;
    }

    public function get_item_by_id($item_id) {
        $result = $this->_db->queryFirst("
            SELECT id_sales_order_item,name, sku FROM ims_sales_order_item WHERE id_sales_order_item = :item_id 
            ", array(
            ':item_id' => $item_id
        ));
        if (isset($result))
            return $result;
        return null;
    }

    public function get_status_history_by_order_item($id_sales_order_item) {
        if (isset($id_sales_order_item)) {
            $results = $this->_db->query("
                SELECT  id_sales_order_item_status, so.`name`, h.created_at
                FROM `ims_sales_order_item_status_history` h 
                INNER JOIN `ims_sales_order_item_status` so ON h.`fk_sales_order_item_status` = so.`id_sales_order_item_status`
                WHERE so.deprecated = 0 AND h.`fk_sales_order_item` = :id_sales_order_item
            	ORDER BY h.created_at DESC
                ", array(
                ':id_sales_order_item' => $id_sales_order_item
            ));

            $exce = $this->_db->getPDOException();
            if ($exce) {
                throw $exce;
            }
            $list_status = array();
            foreach ($results as $status) {
                $list_status[] = array(
                    'id' => $status['id_sales_order_item_status'],
                    'time' => $status['created_at'],
                    'status' => $status['name']
                );
            }
            return $list_status;
        }
    }

    public function get_shipment_provider_by_package($id_package) {
        $results = $this->_db->queryFirst("
            SELECT sp.`id_shipment_provider` ,sp.`shipment_provider_name`, sp.`url`, pd.`tracking_number`
            FROM oms_shipment_provider sp 
            INNER JOIN oms_package_dispatching pd ON pd.fk_shipment_provider = sp.id_shipment_provider
            WHERE pd.fk_package = :id_package
            ", array(':id_package' => $id_package));

        $exce = $this->_db->getPDOException();
        if ($exce) {
            throw $exce;
        }
        return $results;
    }

    public function get_all_shippment_type() {
        $results = $this->_db->query("SELECT id_shipment_provider AS id FROM oms_shipment_provider");
        $exce = $this->_db->getPDOException();
        if ($exce) {
            throw $exce;
        }

        $all_id = array();
        foreach ($results as $id) {
            $all_id[] = $id['id'];
        }

        return $all_id;
    }

    public function get_all_payment_method() {
        $results = $this->_db->query("SELECT name FROM ims_sales_order_process");

        $exce = $this->_db->getPDOException();
        if ($exce) {
            throw $exce;
        }

        $all_id = array();
        foreach ($results as $id) {
            $all_id[] = $id['name'];
        }

        return $all_id;
    }
}

?>
